package org.example.batchutils.utils;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.dynamic.datasource.toolkit.DynamicDataSourceContextHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.example.batchutils.mapper.ExcelDataMapper;
import org.example.batchutils.utils.annotaion.TimeCost;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;
import java.util.concurrent.*;

@Slf4j
@Component
public class ExcelUtils {

    // 注入 MyBatis Mapper 或 SqlSession
    @Autowired
    private ExcelDataMapper excelDataMapper;
    /**
     * 导入Excel文件到数据库
     * @param file Excel文件
     * @param batchSize 批处理大小
     */

    @Autowired
    @Qualifier("threadPoolExecutor")
    private ExecutorService executor;

    @TimeCost
    public void importExcelToDatabase(MultipartFile file, int batchSize)   {



        try (InputStream fis = file.getInputStream();
             Workbook workbook = getWorkbook(fis, file.getName())) {


            List<Future<?>> futures = new ArrayList<>();
            // 遍历所有Sheet
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                String tableName = sheet.getSheetName();

                log.info("正在处理表: " + tableName);
                String currentDatasource = DynamicDataSourceContextHolder.peek();
                Future<?> future = executor.submit(() -> {
                    DynamicDataSourceContextHolder.push(currentDatasource);
                    log.info("当前数据源: " +  DynamicDataSourceContextHolder.peek());
                    importSheetToTable(sheet, tableName, batchSize);
                });
                futures.add(future);
            }
            for (Future<?> future : futures) {
                future.get();
            }
        } catch (Exception e) {
            log.error("导入失败", e);
            throw new RuntimeException("导入失败", e);
        }

    }

    /**
     * 根据文件扩展名创建Workbook
     */
    private Workbook getWorkbook(InputStream fis, String fileName) throws IOException {
//        if (fileName.endsWith(".xlsx")) {
//            return new XSSFWorkbook(fis);
//        } else if (fileName.endsWith(".xls")) {
//            return new HSSFWorkbook(fis);
//        } else {
//            throw new IllegalArgumentException("不支持的文件格式");
//        }
        return new XSSFWorkbook(fis);
    }

    /**
     * 导入单个Sheet到数据库表
     */


    public void importSheetToTable(Sheet sheet, String tableName, int batchSize) {
        // 获取列名和数据行
        Row headerRow = sheet.getRow(0);
        if (headerRow == null) {
            System.out.println("Sheet " + tableName + " 为空，跳过");
            return;
        }

        List<String> columnNames = getColumnNames(headerRow);
        List<Map<String, Object>> batchData = new ArrayList<>();

        Iterator<Row> rowIterator = sheet.iterator();
        // 跳过标题行
        if (rowIterator.hasNext()) {
            rowIterator.next();
        }

        int rowCount = 0;
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            Map<String,Object> rowData = new HashMap<>();

            // 构造行数据
            for (int i = 0; i < columnNames.size(); i++) {
                Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                rowData.put(columnNames.get(i),getCellValue(cell));
            }


            batchData.add(rowData);
            rowCount++;

            // 批量插入
            if (rowCount % batchSize == 0) {
                excelDataMapper.batchInsert(tableName, columnNames, batchData);
                batchData.clear();
                System.out.println("已处理 " + rowCount + " 行数据");
            }
        }

        // 处理剩余数据
        if (!batchData.isEmpty()) {
            excelDataMapper.batchInsert(tableName, columnNames, batchData);
        }

        System.out.println("表 " + tableName + " 导入完成，共 " + rowCount + " 行");
    }

    /**
     * 获取列名列表
     */
    private List<String> getColumnNames(Row headerRow) {
        List<String> columnNames = new ArrayList<>();
        for (Cell cell : headerRow) {
            columnNames.add(getCellValue(cell));
        }
        return columnNames;
    }



    /**
     * 设置PreparedStatement参数值
     */
    private void setPreparedStatementValues(PreparedStatement pstmt, Row row,
                                            List<String> columnNames) throws SQLException {
        for (int i = 0; i < columnNames.size(); i++) {
            Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            String cellValue = getCellValue(cell);
            pstmt.setString(i + 1, cellValue);
        }
    }

    /**
     * 获取单元格值（字符串形式）
     */
    private String getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    // 避免科学计数法
                    double numericValue = cell.getNumericCellValue();
                    if (numericValue == Math.floor(numericValue)) {
                        return String.valueOf((long) numericValue);
                    } else {
                        return String.valueOf(numericValue);
                    }
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    return cell.getStringCellValue();
                } catch (Exception e) {
                    try {
                        return String.valueOf(cell.getNumericCellValue());
                    } catch (Exception ex) {
                        return cell.getCellFormula();
                    }
                }
            default:
                return null;
        }
    }
}